package com.situ.fruit;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.situ.JDBC.JDBCUtil;

public class FruitDao {

	/**
	 * 执行insert-sql插入一条记录
	 * 
	 * @param model
	 * @return
	 * @throws SQLException
	 */
	public Integer insert(FruitModel model) {// add save 面向对象的开发
		String sql = "insert into fruit(name,weight,price,stock,number)values(?,?,?,?,?)";
		Connection conn = JDBCUtil.getConnection();
		PreparedStatement ps = null;
		if (conn == null)
			return null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, model.getName());
			ps.setInt(2, model.getWeight());
			ps.setFloat(3, model.getPrice());
			ps.setInt(4, model.getStock());
			ps.setInt(5, model.getNumber());
			return ps.executeUpdate(); // 执行sql语句，并返回影响的行数
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 根据逻辑主键修改记录其他全部字段
	 * 
	 * @param model
	 * @return
	 */
	public int update(FruitModel model) {
		String sql = "update fruit set stock=?,number=?,count=? where name=?";
		Connection conn = JDBCUtil.getConnection();
		if (conn == null)
			return 0;
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, model.getStock());
			ps.setInt(2, model.getNumber());
			ps.setInt(3, model.getCount());
			ps.setString(4, model.getName());
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(conn, ps);
		}
		return 0;
	}

	/**
	 * 根据单个查询条件
	 * 
	 */
	public List<FruitModel> selectFruitList() {
		String sql = "select name,weight,price,stock,count,number from fruit order by count desc  limit 3";
		Connection conn = JDBCUtil.getConnection();
		if (conn == null)
			return null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<FruitModel> list = new ArrayList<>();
		try {
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			FruitModel f = null;
			while (rs.next()) {// ORM（把数据库的表的信息转换成数据库的过程）
				f = new FruitModel();
				f.setName(rs.getString("name"));
				f.setWeight(rs.getInt("weight"));
				f.setPrice(rs.getFloat("price"));
				f.setStock(rs.getInt("stock"));
				f.setCount(rs.getInt("count"));
				f.setNumber(rs.getInt("number"));
				list.add(f);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(conn, ps, rs);
		}
		return list;
	}

	public List<FruitModel> selectAllFruitList() {
		String sql = "select name,weight,price,stock,count,number from fruit order by count desc";
		Connection conn = JDBCUtil.getConnection();
		if (conn == null)
			return null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<FruitModel> list = new ArrayList<>();
		try {
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			FruitModel f = null;
			while (rs.next()) {// ORM（把数据库的表的信息转换成数据库的过程）
				f = new FruitModel();
				f.setName(rs.getString("name"));
				f.setWeight(rs.getInt("weight"));
				f.setPrice(rs.getFloat("price"));
				f.setStock(rs.getInt("stock"));
				f.setCount(rs.getInt("count"));
				f.setNumber(rs.getInt("number"));
				list.add(f);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(conn, ps, rs);
		}
		return list;
	}

	/**
	 * 根据单个查询条件 - 执行查询sql，返回结果构成的实体类集合
	 * 
	 */
	public List<FruitModel> selectList(String name) {
		// 要进行字符串拼接，使用StringBuffer/StringBuilder
		StringBuffer sql = new StringBuffer("select name,weight,price,stock,count,number from fruit");
		appendWhere(sql, name);
		Connection conn = JDBCUtil.getConnection();
		if (conn == null)
			return null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<FruitModel> list = new ArrayList<>();
		try {
			ps = conn.prepareStatement(sql.toString());
			if (name != null)
				ps.setString(1, name);
			rs = ps.executeQuery();
			FruitModel f = null;
			while (rs.next()) {// ORM（把数据库的表的信息转换成数据库的过程）
				f = new FruitModel();
				f.setName(rs.getString("name"));
				f.setWeight(rs.getInt("weight"));
				f.setPrice(rs.getFloat("price"));
				f.setStock(rs.getInt("stock"));
				f.setCount(rs.getInt("count"));
				f.setNumber(rs.getInt("number"));
				list.add(f);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(conn, ps, rs);
		}
		return list;
	}

	private void appendWhere(StringBuffer sql, String name) {
		sql.append(" where 1=1 ");
		if (name != null && !name.trim().isEmpty()) { // !code.trim().isEmpty()：不是空字符串（即空格一类的）
			sql.append(" and name = ? ");
		}
	}
}
